package com.github.shiqiyue.myadmin.util.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/***
 * 从EXCEL导入到数据库
 *
 * @author wwy
 *
 */
public class ObjectExcelRead {

    /**
     * @param filepath //文件路径
     * @param filename //文件名
     * @param startrow //开始行号
     * @param startcol //开始列号
     * @param sheetnum //sheet
     * @return list
     */
    public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
        List<Object> varList = new ArrayList<>();

        try {
            File target = new File(filepath, filename);
            FileInputStream fi = new FileInputStream(target);
            HSSFWorkbook wb = new HSSFWorkbook(fi);
            // sheet 从0开始
            HSSFSheet sheet = wb.getSheetAt(sheetnum);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            // 行循环开始
            for (int i = startrow; i < rowNum; i++) {

                Map<String, Object> varpd = new HashMap<>(20);
                // 行
                HSSFRow row = sheet.getRow(i);
                // 每行的最后一个单元格位置
                int cellNum = row.getLastCellNum();
                // 列循环开始
                for (int j = startcol; j < cellNum; j++) {
                    HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    String cellValue = "";
                    if (null != cell) {
                        // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                        CellType cellType = cell.getCellTypeEnum();
                        if (cellType.equals(CellType.NUMERIC)) {
                            cellValue = new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString()
                                    .trim();
                        }
                        if (cellType.equals(CellType.STRING)) {
                            cellValue = cell.getStringCellValue().trim();
                        }
                        if (cellType.equals(CellType.FORMULA)) {
                            cellValue = (cell.getNumericCellValue() + "").trim();
                        }
                        if (cellType.equals(CellType.BLANK)) {
                            cellValue = StringUtils.EMPTY;
                        }
                        if (cellType.equals(CellType.BOOLEAN)) {
                            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                        }
                        if (cellType.equals(CellType.ERROR)) {
                            cellValue = String.valueOf(cell.getErrorCellValue()).trim();
                        }
                    }

                    varpd.put("var" + j, cellValue);

                }
                varList.add(varpd);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e);
        }

        return varList;
    }
}
